#!/bin/bash
APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else
   echo "请传入日期参数"
   exit
fi

# 这是sql语句
dwd_test_exam_inc="
insert overwrite table edu.dwd_test_exam_inc partition(dt='${do_date}')
select t1.user_id,
       t1.score,
       t1.score_stage,
       t1.duration_sec,
       t1.paper_id,
       t4.paper_title,
       t4.course_id,
       t5.course_name,
       t2.question_id,
       t2.is_correct,
       t3.question_txt
from (
         select data.user_id,
                data.score,
                floor(data.score / 10) score_stage,
                data.duration_sec,
                data.id,
                data.paper_id,
                date (data.create_time) dt
         from ods_test_exam_inc
         where date(data.create_time) = '${do_date}'
     ) t1
     left join (
        select data.user_id,
               data.exam_id,
               data.paper_id,
               data.question_id,
               data.is_correct
        from ods_test_exam_question_inc
    ) t2
    on t1.id = t2.exam_id
    and t1.user_id = t2.user_id
    and t1.paper_id = t2.paper_id
    left join (
        select id,
               question_txt
        from ods_test_question_info_full
        where dt = '${do_date}'
    ) t3
    on t2.question_id = t3.id
    left join (
        select id,
               paper_title,
               course_id
        from ods_test_paper_full
        where dt = '${do_date}'
    )t4
    on t1.paper_id=t4.id
    left join (
        select id,
               course_name
        from ods_course_info_full
        where dt = '${do_date}'
    )t5
    on t4.course_id=t5.id;
"
dwd_traffic_page_view_inc="
insert overwrite table dwd_traffic_page_view_inc partition (dt='${do_date}')
select
     common.ar  province_id,         --'省份id',
     common.ba  brand,         --'手机品牌',
     common.ch  channel,         --'渠道',
     common.is_new  is_new,     --'是否首次启动',
     common.md  model,         --'手机型号',
     common.mid  mid_id,        --'设备id',
     common.os  operate_system,         --'操作系统',
     common.sc  sourceType_cod,         --来源编码
     common.uid  user_id,        --'会员id',
     common.vc  version_code,         --'app版本号',
     page.item  page_item,         --  '当前页面类型的编码值：11622',
     page.item_type  page_item_type,    -- '页面对象类型（属性内容or button这种玩意儿的id，组成页面的）',
     page.last_page_id  last_page_id, -- '上个页面类型：登录',
     page.page_id  page_id,      --'当前页面类型：首页',
     date_format(from_utc_timestamp(ts, 'Asia/Shanghai'), 'yyyy-MM-dd') date_id ,  --  '日期id',
     date_format(from_utc_timestamp(ts, 'Asia/Shanghai'), 'yyyy-MM-dd HH:mm:ss') view_time,-- '跳入时间',
     common.sid  session_id,        -- '所属会话id',
     page.during_time  during_time  --'持续时间毫秒'
from ods_log_inc
where dt = '${do_date}' and page.page_id is not null;
"

dwd_user_chapter_process_lidan_inc="
insert overwrite table dwd_user_chapter_process_lidan_inc partition (dt = '${do_date}')
select data.id,
       data.course_id,
       data.chapter_id,
       data.user_id,
       data.position_sec,
       data.create_time,
       data.update_time,
       data.deleted
from ods_user_chapter_process_inc
where dt = '${do_date}'
  and type = 'insert'
"
dwd_order_detail_lidan_inc="
insert overwrite table dwd_order_detail_lidan_inc partition (dt='${do_date}')
select data.id,
       data.course_id,
       data.course_name,
       data.order_id,
       data.user_id,
       data.origin_amount,
       data.coupon_reduce,
       data.final_amount,
       data.create_time,
       data.update_time
from ods_order_detail_inc
where dt = '${do_date}'
  and type = 'insert';
"
dwd_review_info_lidan_inc="
insert overwrite table dwd_review_info_lidan_inc partition (dt = '${do_date}')
select data.id,
       data.user_id,
       data.course_id,
       data.review_txt,
       data.review_stars,
       data.create_time,
       data.deleted
from ods_review_info_inc
where dt = '${do_date}'
  and type = 'bootstrap-insert';
"
dwd_trade_cart_add_inc="
with ci as (
    select data.id,
           data.user_id,
           data.course_id,
           date_format(data.create_time, 'yyyy-MM-dd') date_id,
           data.create_time,
           data.session_id
    from ods_cart_info_inc
    where dt = '${do_date}' and type = 'insert'
),lo as (
    select
        common.sid sid,
        common.sc sc_id
    from ods_log_inc
    where dt = '${do_date}'
),sc as (
    select
        id,
        source_site
    from ods_base_source_full
    where dt = '${do_date}'
)
insert overwrite table dwd_trade_cart_add_inc partition (dt='${do_date}')
select ci.id,
       user_id,
       course_id,
       date_id,
       create_time,
       sc.id source_id,
       sc.source_site
from ci left join lo on ci.session_id = lo.sid
        left join sc on lo.sc_id = sc.id;
"
dwd_trade_order_detail_inc="
with od as(
    select
        data.id,
        data.order_id,
        data.course_id,
        date_format(data.create_time,'yyyy-MM-dd') date_id,
        data.create_time,
        data.origin_amount split_origin_amount,
        data.coupon_reduce split_coupon_reduce,
        data.final_amount split_total_amount
    from ods_order_detail_inc
    where dt = '${do_date}' and type = 'insert'
), oi as (
    select
        data.id,
        data.user_id,
        data.province_id,
        data.session_id
    from ods_order_info_inc
    where dt = '${do_date}' and type = 'insert'
),lo as (
    select
        common.sid sid,
        common.sc sc_id
    from ods_log_inc
    where dt = '${do_date}'

),sc as (
    select
        id,
        source_site
    from ods_base_source_full
    where dt = '${do_date}'
)
insert overwrite table dwd_trade_order_detail_inc partition (dt = '${do_date}')
select od.id,
       order_id,
       user_id,
       course_id,
       province_id,
       date_id,
       create_time,
       sc.id,
       source_site,
       cast(split_origin_amount as decimal(16,2)),
       cast(split_coupon_reduce as decimal(16,2)),
       cast(split_total_amount as decimal(16,2))
from od left join oi on od.order_id = oi.id
        left join lo on oi.session_id = lo.sid
        left join sc on lo.sc_id = sc.id
"
dwd_trade_pay_detail_suc_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
with od as(
    select
        data.id,
        data.order_id,
        data.course_id,
        data.origin_amount split_origin_amount,
        data.coupon_reduce split_coupon_reduce,
        data.final_amount split_total_amount
    from ods_order_detail_inc
    where (dt = '${do_date}' or dt = date_sub('${do_date}',1))and (type = 'insert' or  type = 'bootstrap-insert')
),py as (
    select
        data.order_id,
        date_format(data.callback_time,'yyyy-MM-dd') date_id,
        data.callback_time
    from ods_payment_info_inc
    where dt = '${do_date}' and type='update' and array_contains(map_keys(old),'callback_time') and data.callback_time is not null
), oi as (
    select
        data.id,
        data.user_id,
        data.province_id,
        data.session_id
    from ods_order_info_inc
    where dt = '${do_date}' and type='update' and old['order_status'] is not null
),lo as (
    select
        common.sid sid,
        common.sc sc_id
    from ods_log_inc
    where dt = '${do_date}'

),sc as (
    select
        id,
        source_site
    from ods_base_source_full
    where dt = '${do_date}'
)
insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt = '${do_date}')
select od.id,
       py.order_id,
       user_id,
       course_id,
       province_id,
       date_id,
       callback_time,
       sc.id,
       source_site,
       cast(split_origin_amount as decimal(16,2)),
       cast(split_coupon_reduce as decimal(16,2)),
       cast(split_total_amount as decimal(16,2))
from od inner join py on od.order_id = py.order_id
        left join oi on py.order_id = oi.id
        left join lo on oi.session_id = lo.sid
        left join sc on lo.sc_id = sc.id;
"

dwd_user_video_inc="
insert overwrite table dwd_user_video_inc partition (${do_date})
select
    u.id,
    real_name,
    ucp.course_id,
    ucp.chapter_id,
    position_sec,
    create_time,
    video_id,
    during_sec,
    publisher_id,
    chapter_num,
    ${do_date}
from
    (
        select
            data.id id,
            data.real_name real_name,dt
        from ods_user_info_inc where dt = '${do_date}'
    )u
        join
    (
        select
            data.user_id id,
            data.course_id course_id ,
            data.chapter_id	chapter_id,
            data.position_sec position_sec,
            date_format(data.create_time,'yyyy-MM-dd') create_time
        from ods_user_chapter_process_inc where dt = '${do_date}' and type = 'bootstrap-insert'
    )ucp
    on u.id = ucp.id
        join
    (
        select
            data.id video_id,
            data.during_sec during_sec,
            data.publisher_id publisher_id,
            data.course_id course_id,
            data.chapter_id	chapter_id
--     data.chapter_id	chapter_id
        from ods_video_info_inc where dt = '${do_date}' and type = 'bootstrap-insert'
    )vi
    on   ucp.course_id = vi.course_id and ucp.chapter_id =  vi.chapter_id
        join
    (
        select
            id,chapter_num
        from ods_course_info_full
        where dt = '${do_date}'
    )ci
    on ci.id = vi.course_id;
"


case $1 in
    "dwd_test_exam_inc" )
        /opt/module/hive/bin/hive -e "use ${APP};$dwd_test_exam_inc"
    ;;
    "dwd_traffic_page_view_inc" )
        /opt/module/hive/bin/hive -e "use ${APP};$dwd_traffic_page_view_inc"
    ;;
    "dwd_user_chapter_process_lidan_inc" )
        /opt/module/hive/bin/hive -e "use ${APP};$dwd_user_chapter_process_lidan_inc"
    ;;
    "dwd_order_detail_lidan_inc" )
        /opt/module/hive/bin/hive -e "use ${APP};$dwd_order_detail_lidan_inc"
    ;;
    "dwd_review_info_lidan_inc" )
        /opt/module/hive/bin/hive -e "use ${APP};$dwd_review_info_lidan_inc"
    ;;
    "dwd_trade_cart_add_inc" )
        /opt/module/hive/bin/hive -e "use ${APP};$dwd_trade_cart_add_inc"
    ;;
    "dwd_trade_order_detail_inc" )
        /opt/module/hive/bin/hive -e "use ${APP};$dwd_trade_order_detail_inc"
    ;;
    "dwd_trade_pay_detail_suc_inc" )
        /opt/module/hive/bin/hive -e "use ${APP};$dwd_trade_pay_detail_suc_inc"
    ;;
  "dwd_trade_pay_detail_suc_inc" )
        /opt/module/hive/bin/hive -e "use ${APP};$dwd_user_video_inc"
    ;;












    "all" )
        #模板  hive -e "$dwd_xxxx_xxxx$dwd_xxxx_xxxx$dwd_xxxx_xxxx$dwd_xxxx_xxxx"
        /opt/module/hive/bin/hive -e "
        use ${APP};
        $dwd_test_exam_inc
        $dwd_traffic_page_view_inc
        $dwd_user_chapter_process_lidan_inc
        $dwd_order_detail_lidan_inc$dwd_review_info_lidan_inc
        $dwd_trade_cart_add_inc
        $dwd_trade_order_detail_inc
        $dwd_trade_pay_detail_suc_inc
        $dwd_user_video_inc
        "
    ;;
esac